SELECT Department
    , LastName
    , Rate
    , HireDate
    , LAST_VALUE(HireDate) OVER (
        PARTITION BY Department ORDER BY Rate
        ) AS LastValue
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS eph
    ON eph.BusinessEntityID = edh.BusinessEntityID
-- Joining with the Employee table to get employee data
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN ('Information Services', 'Document Control');

